package com.cn.ccp.service.faculty;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.RowMapper;

import com.cn.ccp.bean.GradeBean;
import com.cn.ccp.service.BasicService;
import com.cn.ccp.util.CCPContants;

/**
 * 年级管理
 * @author Administrator
 *
 */
public class GradeService extends BasicService{
	private Log log = LogFactory.getLog(GradeService.class);
		
	/**
	 * 创建班级
	 * @param 
	 * @return
	 */
	public boolean saveGrade(GradeBean bo){
		log.info(bo);
		String sql = "insert into grade_type(specialty_id,grade_type_name,grade_type_descript) values (?,?,?)";
		return jdbcTemplate.update(sql ,new Object[]{bo.getSpecialtyId(),bo.getGradeType(),bo.getDescription()}) > 0;
	}
	
	/**
	 * 更新班级
	 * @param bo
	 * @return
	 */
	public boolean updateGrade(GradeBean bo){
		log.info(bo);
		String sql = "update grade_type set specialty_id = ? , grade_type_name = ?, grade_type_descript = ? where id = ?";
		return jdbcTemplate.update(sql ,new Object[]{bo.getSpecialtyId(),bo.getGradeType(),bo.getDescription(),bo.getId()}) > 0; 
	}
	
	/**
	 * 删除年级
	 * @param id
	 * @return
	 */
	public boolean deleteGrade(int id) {
		String sql = "delete from grade_type where id = ?";
		return jdbcTemplate.update(sql ,new Object[]{id}) > 0;
	}
	
	/**
	 *根据id查询年级
	 * @param id
	 * @return
	 */
	public Object findGradeById(int id) {
		String sql = "SELECT * FROM grade_type WHERE id = ?";
		return jdbcTemplate.queryForObject(sql, new Object[] { id }, new GradeMapper());
	}
	
	@SuppressWarnings("unchecked")
	public List pageQueryGrade(int start, int pagesize) {
		String sql = "";
		Object[] objs =null ;
		if(CCPContants.getUserBean().getUserName().equalsIgnoreCase("admin") ){
			sql = "SELECT gt.* ,s.specialtyname FROM grade_type gt "+
			" LEFT JOIN specialty s ON s.id = gt.specialty_id "+
			"  limit ?,?";
			objs = new Object[] { start,pagesize} ;
		}else{
			sql = "SELECT gt.* ,s.specialtyname FROM grade_type gt "+
			" LEFT JOIN specialty s ON s.id = gt.specialty_id "+
			" WHERE s.faculty_id = ? limit ?,? ";
			objs = new Object[] {CCPContants.getUserBean().getFacultyId(), start,pagesize};
		}
		return jdbcTemplate.query(sql, objs,new GradeMapper());
	}
	
	/**
	 * 根据专业id，查询班级
	 * @param specialtyId
	 * @return
	 */
	public List getGradeBySpecialtyId(String specialtyId) {
		String sql = "select * from grade_type  where specialty_id = ?";
		return jdbcTemplate.query(sql, new Object[] {specialtyId},new GradeMapper());
	}
	
	/**
	 * 查询所有班级
	 * @param specialtyId
	 * @return
	 */
	public List getAllGrade(String specialtyId) {
		String sql = "select * from grade_type where specialty_id = ? ";
		return jdbcTemplate.query(sql, new Object[] {specialtyId},new GradeMapper());
	}
	
	public int getCountGrade() {
		String sql = "";
		Object[] objs =null ;
		if(CCPContants.getUserBean().getUserName().equalsIgnoreCase("admin") ){
			sql = "SELECT count(1) FROM grade_type gt "+
			" LEFT JOIN specialty s ON s.id = gt.specialty_id ";
			objs = new Object[] { } ;
		}else{
			sql = "SELECT count(1) FROM grade_type gt "+
			" LEFT JOIN specialty s ON s.id = gt.specialty_id "+
			" WHERE s.faculty_id = ?  ";
			objs = new Object[] {CCPContants.getUserBean().getFacultyId()};
		}
		
		return jdbcTemplate.queryForInt(sql,objs);
	}
	
	
	public boolean gradeNameIsAlready(int id ,String name) {
		String sql = "SELECT * FROM grade_type WHERE grade_type_name = ? and id != ?";
		return jdbcTemplate.query(sql, new Object[] { name, id }, new GradeMapper()).size() > 0;
	}
	
	public int gradeIdByName(String name) {
		String sql = "SELECT id FROM grade_type WHERE grade_type_name = ? ";
		return (Integer) jdbcTemplate.queryForObject(sql, new Object[] { name },Integer.class);
	}
	
	public List<GradeBean> getAllGrade(){
		String sql = "select * from grade_type ";
		return jdbcTemplate.query(sql,new GradeMapper());
	}
	
	
	public List<GradeBean> geListGradeBySpecialty(String specialtyid){
		String sql = "SELECT gt.* ,cm.id AS cmid FROM grade_type gt "+
						" LEFT OUTER JOIN course_mapping cm ON cm.other_id = gt.id AND cm.course_mapping_type = 2 "+
						" WHERE gt.specialty_id = ? "+
						" ORDER BY  gt.specialty_id";
		return jdbcTemplate.query(sql ,new Object[]{specialtyid} ,new GradeMapper());
	}
	
	
	public List<GradeBean> geListGradeBySpecialty(String specialtyid,String courseId){
//		String sql = "SELECT gt.* ,cm.id AS cmid FROM grade_type gt "+
//						" LEFT OUTER JOIN course_mapping cm ON cm.other_id = gt.id AND cm.course_mapping_type = 2 and cm.course_id = ? "+
//						" WHERE gt.specialty_id = ? "+
//						" ORDER BY  gt.specialty_id";
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT gt.* ,cm.id AS cmid FROM grade_type gt  ");
		sql.append(" LEFT OUTER JOIN course_mapping cm ON  cm.course_id = ? and  ");
		sql.append(" (");
		sql.append(" (cm.other_id = gt.id AND cm.course_mapping_type = 2) ");
		sql.append(" or( cm.other_id in (select id from user where grade = gt.id )) ");
		sql.append(" ) ");
		sql.append(" WHERE gt.specialty_id = ?  ");
		sql.append(" group by gt.id ORDER BY  gt.specialty_id ");

		return jdbcTemplate.query(sql.toString() ,new Object[]{courseId,specialtyid} ,new GradeMapper());
	}
	
	/**
	 * 年级封装类
	 * @author Administrator
	 *
	 */
	public class GradeMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			GradeBean bean = new GradeBean();
			bean.setId(rs.getInt("id"));
			bean.setSpecialtyId(rs.getString("specialty_id"));
			bean.setGradeType(rs.getString("grade_type_name"));
			bean.setDescription(rs.getString("grade_type_descript"));
			try {
				bean.setSpecialtyName(rs.getString("specialtyname"));
			} catch (Exception e) {
			}
			try {
				String cmid = rs.getString("cmId");
				if(cmid != null  && !cmid.equals(""))
					bean.setReleaseSelect(true);
			} catch (Exception e) {
				// TODO: handle exception
			}
			return bean;
		}
	}
	
}
